import random
import time
from datetime import datetime, timedelta
import mysql.connector
import pandas as pd
import requests
from mysql.connector import Error
from x01_stock.xx_util.io_util import create_directory, get_content_form_file


# 20240726  此模块已经可以根据传入的开始日期与结束日期获取深交所的融资融券数据，并导入数据库
# 20240727 此版本不在一次性下载后再导入数据库，采用边下载，边导入的方式
def is_trading_day(date):
    """
    判断是否交易日，如果是，返回true
    :param date:
    :return:
    """
    # 判断日期是否为周末
    if date.weekday() >= 5:  # 5和6分别代表周六和周日
        return False
    # 读取国家规定的节假日列表
    holidays = get_content_form_file('../../holiday.txt')
    # 如果存在特定的法定节假日列表，可以在此处进行判断
    if holidays.find(date.strftime('%Y-%m-%d')) != -1:
        return False
    # 如果以上条件都不满足，则认为是法定工作日
    return True


def download_file(url, file_full_path):
    """
    下载文件
    :param url: 文件的URL
    :param file_name: 保存文件的名称
    """
    # 发起GET请求
    response = requests.get(url, stream=True)
    # print(response)
    # 确保请求成功,如果不成功会抛出异常
    response.raise_for_status()

    # 使用with语句打开文件，确保正确关闭
    with open(file_full_path, 'wb') as file:
        # 分块写入文件
        for chunk in response.iter_content(chunk_size=8192):
            if chunk:  # 过滤掉keep-alive新发送的空包
                file.write(chunk)
    print('文件下载完成。。。')
    return file_full_path


def insert_excel_data_to_mysql(filename, stat_date):
    # 连接数据库
    mydb = mysql.connector.connect(
        host='localhost',  # 数据库主机地址
        user='root',  # 数据库用户名
        password='123',  # 数据库密码
        database='z_sproot_series'  # 数据库名称
    )

    # 创建游标对象
    mycursor = mydb.cursor()

    try:
        # 读取Excel文件 dtype={'证券代码': str} 设置以字符串格式读取
        df = pd.read_excel(filename, dtype={'证券代码': str})
    except FileNotFoundError:
        print(f"文件 {filename} 不存在，已忽略。")
        return
    # 将日期时间类型的列转换为字符串类型
    for col in df.columns:  # 遍历DataFrame中的每一列
        print(f"Column Name: {col}")
        # 把数字中的 "," 替换掉（删除）
        if col != '证券代码' and col != '证券简称':
            df[col] = df[col].str.replace(",", "")  #df[col].replace(",", "")  # 打印出当前列的所有数据

        # if df[col].dtype == 'datetime64[ns]':  # 如果该列的数据类型是日期时间类型
        #     df[col] = df[col].astype(str)  # 将该列的数据类型转换为字符串类型

    # 遍历Excel表格中的每一行，并将每一行插入到数据库中
    for row in df.itertuples(index=False):  # 遍历DataFrame中的每一行
        print(row)
        sql = f"INSERT INTO stock_rzrq (stat_date,stock_code,stock_name,rzmr,rzye,rqmc,rqyl,rqye,rzrqye) VALUES ('{stat_date}',%s, %s, %s, %s,%s, %s, %s, %s)"  # SQL插入语句
        # sql = "INSERT INTO " + table + "(stat_date,stock_code,stock_name,rzmr,rzye,rqmc,rqyl,rqye,rzrqye) VALUES (" + ss + ",%s, %s, %s, %s,%s, %s, %s, %s)"  # SQL插入语句
        print(sql)
        val = row  # 插入的数据
        mycursor.execute(sql, val)  # 执行SQL插入语句
        print("正在插入数据:", val)  # 输出正在插入的数据

    # 提交更改并关闭数据库连接
    mydb.commit()  # 提交更改
    mycursor.close()  # 关闭游标对象
    mydb.close()  # 关闭数据库连接
    print('数据已插入数据库')


# 循环跳出的日期，当前日期加一天
def download_rzrq(start_date, end_date, file_save_path):
    """
    :param start_date:下载的开始日期
    :param end_date: 下载的结束日期
    :param file_save_path: 文件存储的路径
    :return: 返回[文件路径,交易日期]格式的二维数组
    """
    all_files = []
    # 开始循环处理
    while start_date < end_date:
        print(start_date)
        # 如果是工作日，则开启下载
        if is_trading_day(start_date):
            random_str = ''.join([str(random.randint(0, 9)) for _ in range(16)])
            # 拼接url url = 'https://www.szse.cn/api/report/ShowReport?SHOWTYPE=xlsx&CATALOGID=1837_xxpl&txtDate=2024-07-08&tab2PAGENO=1&random=0.9970942990905536&TABKEY=tab2'
            url = 'https://www.szse.cn/api/report/ShowReport?SHOWTYPE=xlsx&CATALOGID=1837_xxpl&txtDate=' + start_date.strftime(
                '%Y-%m-%d') + '&tab2PAGENO=1&random=0.' + random_str + '&TABKEY=tab2'
            print(url)
            # 防止传入的路径中有文件夹不存在
            create_directory(file_save_path)
            file_path = download_file(url, file_save_path + r'\sz_' + start_date.strftime('%Y-%m-%d') + '.xlsx')
            print(file_path)
            # 将数据插入数据库
            insert_excel_data_to_mysql(file_path, start_date)
            # 开启休眠防止被拦截
            sleep = random.randint(1, 40)
            print('开启休眠防止被拦截，准备休眠--' + str(sleep))
            time.sleep(sleep)
        start_date = start_date + timedelta(days=1)
    print('下载与插入数据库阶段完成...开始存储过程处理...')
    return True


def call_proc_update_stock_price(stat_date):
    # 连接数据库
    mydb = mysql.connector.connect(
        host='localhost',  # 数据库主机地址
        user='root',  # 数据库用户名
        password='123',  # 数据库密码
        database='z_sproot_series'  # 数据库名称
    )
    # 创建游标对象
    my_cursor = mydb.cursor()

    # 调用存储过程
    proc_name = 'update_rzrq_stat_date'
    # 元组只有一个元素时需要保留最后面的 ，
    args = (stat_date,)  # 存储过程参数
    my_cursor.callproc(proc_name, args)
    # # 获取存储过程返回的输出参数（如果有的话）
    # mycursor.execute("SELECT @_your_procedure_name_param_name")
    # for (output_value,) in mycursor:
    #     print(output_value)

    # 提交更改并关闭数据库连接
    mydb.commit()  # 提交更改
    my_cursor.close()  # 关闭游标对象
    mydb.close()  # 关闭数据库连接
    print('存储过程执行完毕...')


def get_actual_download_day(date):
    if date.weekday() == 0:
        print(date.weekday())
        return date + timedelta(days=-3)
    elif date.weekday() <= 4:
        return date + timedelta(days=-1)
    else:  # 如果是周六日运行程序，由于深交所是下一个交易日才出数据，运行程序并无作用。因此不返回日期格式的具体日期
        return False


def fetch_data_from_mysql(host_name, user_name, user_password, db_name, query):
    """
    从MySQL数据库查询数据
    """
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        if connection.is_connected():
            db_Info = connection.get_server_info()
            print("Connected to MySQL Server version ", db_Info)
            cursor = connection.cursor()
            cursor.execute(query)
            records = cursor.fetchall()

            # 使用pandas的DataFrame来存储数据
            # 设置默认表头
            # columns = [col[0] for col in cursor.description]
            # 手动设置表头 `id`, `stat_date`, `stock_code`, `stock_name`, `stock_price`, `stock_price_pct`, `rzrqye`,`lrzl`,`rzjmr`, `rzche`,`rzmr`, `rzye`,`rqjmc`, `rqche`,`rqmc`, `rqyl`, `rqye`
            columns = ['id', '交易日期', '股票代码', '股票名称', '股价', '今日涨幅', '融资融券余额', '两融增量',
                       '融资净买入', '融资偿还额', '融资买入', '融资余额', '融券净卖出', '融券偿还额', '融券卖出',
                       '融券余量', '融券余额']
            df = pd.DataFrame(records, columns=columns)
            df['股价'] = pd.to_numeric(df['股价'], errors='coerce')
            df['今日涨幅'] = pd.to_numeric(df['今日涨幅'], errors='coerce')
            df['融资融券余额'] = (pd.to_numeric(df['融资融券余额'], errors='coerce') / 10000).round(2)
            df['两融增量'] = (pd.to_numeric(df['两融增量'], errors='coerce') / 10000).round(2)
            df['融资净买入'] = (pd.to_numeric(df['融资净买入'], errors='coerce') / 10000).round(2)
            df['融资偿还额'] = (pd.to_numeric(df['融资偿还额'], errors='coerce') / 10000).round(2)
            df['融资买入'] = (pd.to_numeric(df['融资买入'], errors='coerce') / 10000).round(2)
            df['融资余额'] = (pd.to_numeric(df['融资余额'], errors='coerce') / 10000).round(2)
            df['融券净卖出'] = (pd.to_numeric(df['融券净卖出'], errors='coerce') / 10000).round(2)
            df['融券偿还额'] = (pd.to_numeric(df['融券偿还额'], errors='coerce') / 10000).round(2)
            df['融券卖出'] = (pd.to_numeric(df['融券卖出'], errors='coerce') / 10000).round(2)
            df['融券余量'] = (pd.to_numeric(df['融券余量'], errors='coerce') / 10000).round(2)
            df['融券余额'] = (pd.to_numeric(df['融券余额'], errors='coerce') / 10000).round(2)
            print('数据查询已完成')
            return df
    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")


def export_to_excel(data, excel_path):
    """
    将数据导出到Excel文件
    """
    data.to_excel(excel_path, index=False)
    print(f"Data exported to {excel_path}")


# 每一个交易日获取上一个交易日的融资融券数据导入数据库，处理后生成excel表格
if __name__ == '__main__':
    # 开始日期 datetime(2024, 7, 26).date() #
    today = datetime.today().date()
    prev_trading_day = get_actual_download_day(today)
    print("prev_trading_day" + str(prev_trading_day))
    print('prev_trading_day' + str(prev_trading_day + timedelta(days=1)))
    output_file_path = r'D:\OneDrive\#个人管理\光线传媒.xlsx'

    # 如果要处理的日期不是周六日
    if prev_trading_day is not False:
        # 非国家规定的节假日
        if is_trading_day(prev_trading_day):
            # 存储路径
            file_save_path = r'D:\workspace\python_all\zz_download_file\rzrq'
            # 下载文件，并将数据导入mysqldb  默认指定单日，如果需要多日区间，则调整timedelta(days=1)
            is_finish = download_rzrq(prev_trading_day, prev_trading_day + timedelta(days=1), file_save_path)
            if is_finish:
                # 开始调用存储过程处理股价与净买入净卖出的计算
                call_proc_update_stock_price(prev_trading_day)
                # 数据处理完成，生成需要的股票融资融券数据
                host = "localhost"  # 数据库主机地址
                user = "root"  # 数据库用户名
                password = "123"  # 数据库密码
                database = "z_sproot_series"  # 数据库名称

                query = """SELECT id, stat_date, stock_code, stock_name, stock_price, stock_price_pct,
                                rzrqye,lrzl,rzjmr, rzche,rzmr, rzye,rqjmc, rqche,rqmc, rqyl, rqye
                                FROM stock_rzrq a where stock_code = '300251' order by stat_date desc """

                data = fetch_data_from_mysql(host, user, password, database, query)
                if data is not None:
                    print('导出数据到excel文件')
                    export_to_excel(data, output_file_path)

    else:
        print('需要处理的日期非交易日期，(日期是周六日),程序退出')
        exit(0)
